import 'dart:io';
import 'package:path/path.dart' as path;

import 'package:sqlite3/sqlite3.dart';

String sqliteUtil(int index) {
  String response;
  try {
    response = openDatabse(index);
  } on Exception catch (e) {
    print('发生错误${e}');
    response = e.toString();
  }
  return response;
}

String openDatabse(int index) {
  print("数据库测试开始");
  String databasePath = path.join(path.current, 'my_sqlite_database.db');
  print("文件安装路径:\t${path.current}");
  print("数据库文件存储路径:\t${databasePath}");
  // Database db = sqlite3.open("my_sqlite_database_1.db");
  Database db = sqlite3.open(databasePath);
  String response = '文件存储位置：${path.current}\n数据库存储文职：${databasePath}';

  // 查询有哪些表
  ResultSet tableList = db.select("SELECT name FROM sqlite_master;");
  for (final Row row in tableList) {
    print('table_name[${row['name']}]');
  }

  // Create a table and insert some data
  String tableName = 'artists_${index}';
  String initSql =
      'CREATE TABLE $tableName (id INTEGER NOT NULL PRIMARY KEY,name TEXT NOT NULL);';
  print(initSql);
  db.execute(initSql);

  // Prepare a statement to run it multiple times:
  final stmt = db.prepare('INSERT INTO $tableName (name) VALUES (?)');
  stmt
    ..execute(['The Beatles'])
    ..execute(['Led Zeppelin'])
    ..execute(['The Who'])
    ..execute(['Nirvana']);

  // Dispose a statement when you don't need it anymore to clean up resources.
  stmt.dispose();

  // You can run select statements with PreparedStatement.select, or directly
  // on the database:
  final ResultSet resultSet =
      db.select('SELECT * FROM $tableName WHERE name LIKE ?', ['The %']);

  // You can iterate on the result set in multiple ways to retrieve Row objects
  // one by one.
  response += '\n里面的数据\n';
  for (final Row row in resultSet) {
    String resData = 'Artist[id: ${row['id']}, name: ${row['name']}]\n';
    print(resData);
    response += resData;
  }

  // Register a custom function we can invoke from sql:
  db.createFunction(
    functionName: 'dart_version',
    argumentCount: const AllowedArgumentCount(0),
    function: (args) => Platform.version,
  );
  print(db.select('SELECT dart_version()'));

  // Don't forget to dispose the database to avoid memory leaks
  db.dispose();
  return response;
}
